﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Configuration;
using IronOne.SalesOne.BusinessObjects;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqUserTypes
    {
        public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);

        public List<UserType> GetUserTypes()
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<UserTypeEntity> query = db.UserTypeEntities;                
                return query.Select(c => Mapper.ToUserTypeObject(c)).ToList();
            }
        }

        public List<UserType> GetUserTypes(string sortExpression)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<UserTypeEntity> query = db.UserTypeEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "UserTypeName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.UserTypeName);
                            else
                                query = query.OrderByDescending(c => c.UserTypeName);
                            break;

                        case "UserTypeId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.UserTypeId);
                            else
                                query = query.OrderByDescending(c => c.UserTypeId);
                            break;
                    }
                }
                return query.Select(c => Mapper.ToUserTypeObject(c)).ToList();
            }
        }

        public List<UserType> GetUserTypes(string sortExpression,int pageNo)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<UserTypeEntity> query = db.UserTypeEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "UserTypeName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.UserTypeName);
                            else
                                query = query.OrderByDescending(c => c.UserTypeName);
                            break;

                        case "UserTypeId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.UserTypeId);
                            else
                                query = query.OrderByDescending(c => c.UserTypeId);
                            break;
                    }
                }

                var pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                return pageData.Select(c => Mapper.ToUserTypeObject(c)).ToList();
            }
        }

        public UserType GetUserType(int userTypeId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToUserTypeObject(db.UserTypeEntities
                            .SingleOrDefault(p => p.UserTypeId == userTypeId));
            }
        }

        public List<UserType> GetUserTypeByName(string userTypeName)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<UserTypeEntity> query = db.UserTypeEntities;

                query = from p
                       in db.UserTypeEntities
                        where p.LoweredUserTypeName == userTypeName.ToLower() 
                        select p;

                return query.Select(p=> Mapper.ToUserTypeObject(p)).ToList();
            }
        }

        public List<UserType> GetUserTypesForUser(string sortExpression,string userName)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<UserTypeEntity> query = db.UserTypeEntities;

                //query = from utp in db.UserTypeEntities
                //        join riut in db.RolesInUserTypeEntities
                //        on utp.UserTypeId
                //        equals riut.UserTypeId
                //        where riut.RoleId==

                //        (from aspr in db.aspnet_RoleEntities
                //         join aspuir in db.aspnet_UsersInRoles
                //         on aspr.RoleId
                //             equals aspuir.RoleId
                //             where aspuir.UserId==(
                //                from aspu in db.aspnet_Users
                //                where aspu.UserName==userName
                //                select new{aspu.UserId}
                //             )
                //         select new{}   
                //        )

                //        select utp;

                query = (from userType in db.UserTypeEntities
                        join rolesInUserTypes in db.RolesInUserTypeEntities on userType.UserTypeId equals rolesInUserTypes.UserTypeId
                        join roles in db.aspnet_RoleEntities on rolesInUserTypes.RoleId equals roles.RoleId
                        join usersInRoles in db.aspnet_UsersInRoles on roles.RoleId equals usersInRoles.RoleId
                        join users in db.aspnet_Users on usersInRoles.UserId equals users.UserId
                        where users.UserName == userName
                        select userType).Distinct();

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "UserTypeName":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.UserTypeName);
                            else
                                query = query.OrderByDescending(c => c.UserTypeName);
                            break;

                        case "UserTypeId":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.UserTypeId);
                            else
                                query = query.OrderByDescending(c => c.UserTypeId);
                            break;
                    }
                }
                return query.Select(c => Mapper.ToUserTypeObject(c)).ToList();
            }
        }

        public UserType GetUserTypeForUser(string userName)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<UserTypeEntity> query = db.UserTypeEntities;      

                query = (from userType in db.UserTypeEntities
                        join usersInStructure in db.UsersInStructureEntities on userType.UserTypeId equals usersInStructure.UserTypeId
                         join users in db.aspnet_Users on usersInStructure.UserId equals users.UserId
                         where users.UserName == userName
                         select userType).Distinct();

                return Mapper.ToUserTypeObject(query.SingleOrDefault());
            }
        }

        public void InsertUserType(UserType usertype)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    UserTypeEntity entity = Mapper.ToUserTypeEntity(usertype);
                    db.UserTypeEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    usertype.UserTypeId = entity.UserTypeId;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to user type record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to Insert user type " + usertype.UserTypeName);
                }
            }
        }

        public int UpdateUserType(UserType usertype)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    UserTypeEntity entity = Mapper.ToUserTypeEntity(usertype);
                    db.UserTypeEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to user type record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public int DeleteUserType(UserType usertype)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    UserTypeEntity entity = Mapper.ToUserTypeEntity(usertype);
                    db.UserTypeEntities.Attach(entity, false);
                    db.UserTypeEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to user type record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

    }
}
